package com.contest.repository;

import com.contest.entity.Chapter;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface ChapterRepository extends JpaRepository<Chapter, Long> {
    
    /**
     * 根据课程ID查找章节列表，按顺序排序
     */
    List<Chapter> findByCourseIdOrderByOrderIndexAsc(Long courseId);
    
    /**
     * 分页根据课程ID查找章节
     */
    Page<Chapter> findByCourseIdOrderByOrderIndexAsc(Long courseId, Pageable pageable);
    
    /**
     * 根据课程ID和章节ID查找章节
     */
    Optional<Chapter> findByIdAndCourseId(Long id, Long courseId);
    
    /**
     * 根据课程ID统计章节数量
     */
    long countByCourseId(Long courseId);
    
    /**
     * 查找课程中最大的排序索引
     */
    @Query("SELECT COALESCE(MAX(c.orderIndex), 0) FROM Chapter c WHERE c.courseId = :courseId")
    Integer findMaxOrderIndexByCourseId(@Param("courseId") Long courseId);
    
    /**
     * 查找指定课程中指定排序索引之后的所有章节
     */
    List<Chapter> findByCourseIdAndOrderIndexGreaterThanOrderByOrderIndexAsc(Long courseId, Integer orderIndex);
    
    /**
     * 查找指定课程中指定排序索引之前的所有章节
     */
    List<Chapter> findByCourseIdAndOrderIndexLessThanOrderByOrderIndexAsc(Long courseId, Integer orderIndex);
    
    /**
     * 查找课程中第一个章节
     */
    Optional<Chapter> findFirstByCourseIdOrderByOrderIndexAsc(Long courseId);
    
    /**
     * 查找课程中最后一个章节
     */
    Optional<Chapter> findFirstByCourseIdOrderByOrderIndexDesc(Long courseId);
    
    /**
     * 查找包含PDF文件的章节
     */
    List<Chapter> findByCourseIdAndPdfFilePathIsNotNullOrderByOrderIndexAsc(Long courseId);
    
    /**
     * 查找包含Dockerfile的章节
     */
    List<Chapter> findByCourseIdAndDockerfileIsNotNullOrderByOrderIndexAsc(Long courseId);
    
    /**
     * 根据标题模糊查询课程中的章节
     */
    List<Chapter> findByCourseIdAndTitleContainingIgnoreCaseOrderByOrderIndexAsc(Long courseId, String title);
    
    /**
     * 检查课程中是否存在指定排序索引的章节
     */
    boolean existsByCourseIdAndOrderIndex(Long courseId, Integer orderIndex);
    
    /**
     * 更新指定课程中大于等于指定排序索引的章节的排序索引（批量+1）
     */
    @Modifying
    @Query("UPDATE Chapter c SET c.orderIndex = c.orderIndex + 1 WHERE c.courseId = :courseId AND c.orderIndex >= :orderIndex")
    void incrementOrderIndexFromPosition(@Param("courseId") Long courseId, @Param("orderIndex") Integer orderIndex);
    
    /**
     * 更新指定课程中大于指定排序索引的章节的排序索引（批量-1）
     */
    @Modifying
    @Query("UPDATE Chapter c SET c.orderIndex = c.orderIndex - 1 WHERE c.courseId = :courseId AND c.orderIndex > :orderIndex")
    void decrementOrderIndexFromPosition(@Param("courseId") Long courseId, @Param("orderIndex") Integer orderIndex);
    
    /**
     * 根据课程ID删除所有章节
     */
    void deleteByCourseId(Long courseId);
    
    /**
     * 查找指定排序索引范围内的章节
     */
    List<Chapter> findByCourseIdAndOrderIndexBetweenOrderByOrderIndexAsc(Long courseId, Integer startIndex, Integer endIndex);
    
    /**
     * 查找课程中下一个章节
     */
    Optional<Chapter> findFirstByCourseIdAndOrderIndexGreaterThanOrderByOrderIndexAsc(Long courseId, Integer orderIndex);
    
    /**
     * 查找课程中上一个章节
     */
    Optional<Chapter> findFirstByCourseIdAndOrderIndexLessThanOrderByOrderIndexDesc(Long courseId, Integer orderIndex);
}